import xlrd
import xlwt
import os


class excel:
    def __init__(self, excelFilePath):
        self.excelFile = xlrd.open_workbook(excelFilePath)
        self.excelWrite = xlwt.Workbook()
        self.table = self.excelWrite.add_sheet('sheet')
        self.excelTable = None


    # 获取所有sheet名字
    def getTables(self):
        return self.excelFile.sheet_names()

    # 更改sheet
    def changeTable(self, tableName):
        self.excelTable = self.excelFile.sheet_by_name(tableName)

    def getRowNum(self):
        return self.excelTable.nrows

    def getRowValues(self, rowIndex):
        return self.excelTable.row_values(rowIndex)

    def getSheetsNum(self):
        return self.excelFile.nsheets

    def saveExcel(self,name):
        return self.saveExcel(name)

    def writeSheets(self,start,end,data):
        self.table.write(start,end,data)
        #self.excelWrite.save(name)


    #根据sheet名字返回sheet位置
    def get_sheet_index_by_name(self, sheet_name):
        # 获取所有 sheet 名称列表
        sheet_names = self.getTables()
        # 遍历列表，找到对应名称的索引
        for index, name in enumerate(sheet_names):
            if name == sheet_name:
                return index
        # 如果未找到，返回 -1 或抛出异常
        return -1  # 或 raise ValueError(f"Sheet '{sheet_name}' not found")

    """
    指定查询列，查询值 查找excel是否存在对应行数据，返回一行 list
    col_index ：指定的列
    search_str ： 指定列查找的值
    return 记录，所在数据所在sheet页。 找不到数据时sheet为最大
    """

    def findData(self, col_index, search_str, *args):
        # 是否找见
        result = []
        # 获取sheet数量
        sheetsNum = self.excelFile.nsheets
        # print(sheetsNum)
        for sheetNum in range(0, sheetsNum):
            # 获取工作表
            sheet = self.excelFile.sheet_by_index(sheetNum)
            # 获取工作表的总行数
            nrows = sheet.nrows
            # 循环遍历每一行
            for i in range(1, nrows):
                # 获取指定列的值
                cell_value = sheet.cell_value(i, col_index)
                # 判断是否包含指定字符串
                if search_str == cell_value:
                    # 输出符合条件的记录
                    result = sheet.row_values(i)
                    break

        return result, sheetNum

    """
    文件第一行 根据列名返回列所在位置, list
    """

    def findIndex(self, sheetNum, sheetList, *args):
        # 获取sheet数量
        self.excelFile.sheet_by_name(self.excelFile.sheet_names()[sheetNum])
        list = []
        for i in sheetList:
            find_index = self.excelTable.row_values(0).index(i)
            list.append(find_index)
        return list

    """
    取出excel数据
    *args: 输入待取出的值，list
    **kwargs：条件
    """

    def getExcelData(self,sh='0', *args, **kwargs):
        # lists = list(args)
        # if sh=='0':
        #     num = 0
        # else:
        #     num=self.get_sheet_index_by_name(sh)
        # print(num,type(num))
        # self.changeTable(self.getTables()[num])
        # new_list = self.findIndex(num, lists)
        #
        # """
        # 循环遍历excel中的数据放入列表中，只支持第一个sheet页
        # """
        # # 获取工作表
        # sheet = self.excelFile.sheet_by_index(num)
        # # 获取工作表的总行数
        # nrows = sheet.nrows
        # # 取出列值放入新列表
        # resultList1 = []
        #
        # # 循环遍历每一行
        # for i in range(1, nrows):
        #     result1 = []
        #     for a in new_list:
        #         if len(kwargs)==0:
        #             # 获取指定列的值
        #             cell_value = sheet.cell_value(i, a)
        #             result1.append(cell_value)
        #         else:
        #             b=0
        #             for dict in kwargs:
        #                 dictList = []
        #                 dictList.append(dict)
        #                 if sheet.cell_value(i, self.findIndex(0,dictList)[0])==kwargs[dict]:
        #                     cell_value = sheet.cell_value(i, a)
        #                     b+=1
        #             if b == len(kwargs):
        #                 result1.append(cell_value)
        #     #print(result1)
        #     if len(result1) !=0:
        #         resultList1.append(result1)
        # return resultList1,len(resultList1)


        lists = list(args)
        if sh == '0':
            num = 0
        else:
            num = self.get_sheet_index_by_name(sh)
        print(num, type(num))
        self.changeTable(self.getTables()[num])
        new_list = self.findIndex(num, lists)

        sheet = self.excelFile.sheet_by_index(num)
        nrows = sheet.nrows
        resultList1 = []

        for i in range(1, nrows):
            result1 = []
            for a in new_list:
                if len(kwargs) == 0:
                    # 直接获取指定列的值，并去除左右空格（如果是字符串类型）
                    cell_value = sheet.cell_value(i, a)
                    cell_value = cell_value.strip() if isinstance(cell_value, str) else cell_value
                    result1.append(cell_value)
                else:
                    b = 0
                    for dict_key in kwargs:
                        # 先检查是否满足条件列的值是否匹配（需去除空格）
                        target_col = self.findIndex(0, [dict_key])[0]
                        condition_value = sheet.cell_value(i, target_col)
                        condition_value = condition_value.strip() if isinstance(condition_value,
                                                                                str) else condition_value

                        if condition_value == kwargs[dict_key]:
                            b += 1
                    # 如果所有条件满足，再处理当前列的值并去除空格
                    if b == len(kwargs):
                        cell_value = sheet.cell_value(i, a)
                        cell_value = cell_value.strip() if isinstance(cell_value, str) else cell_value
                        result1.append(cell_value)
            if len(result1) != 0:
                resultList1.append(result1)
        return resultList1, len(resultList1)


if __name__ == '__main__':
    # excelpath = input('目标excel文件地址:')
    # folderpath = input('所有附件地址：')
    # # oo = excel("/Users/chenliang/Documents/浩鲸/OA/历史表单迁移/DingTalk Form Extract/HR HERE_2023.xlsx")
    # oo = excel(excelpath)
    #
    # # folderpath = '/Users/chenliang/Documents/浩鲸/OA/历史表单迁移/DingTalk Form Extract/HR2023'
    # input_str = input("请输入附件所在列名（元素之间用英文逗号分隔）：")
    # input_list = input_str.split(",")
    #
    # # 计数 total 总数， num1 无需处理的， num2 处理的 ， num3 附件名称重复， num4 未找见的
    # total = 0
    # num1 = 0
    # num2 = 0
    # num3 = 0
    # num4 = 0
    #
    # # 取文件夹下所有文件夹名称
    # allfolderTtems = os.listdir(folderpath)
    #
    # # 循环文件夹
    # for item1 in allfolderTtems:
    #     # 存放附件的地址
    #     folderFile = os.path.join(folderpath, item1)
    #     # 附件文件夹下所有附件, 异常跳过
    #     try:
    #         Files = os.listdir(folderFile)
    #         """
    #         根据文件夹名称，获取excel中的附件列表  newList
    #         """
    #         # 根据文件夹名称查找excel中对应的数据
    #         list, sheetNum = oo.findData(0, item1)
    #
    #         # 根据列名查询附件，合并成列表, newList
    #         oo.changeTable(oo.getTables()[sheetNum])
    #         # indexList = oo.findIndex(sheetNum, 'HRFC Attachment','Attachment')
    #         indexList = oo.findIndex(sheetNum, input_list)
    #         newList = []
    #         for i in indexList:
    #             newList += list[i].split(sep='\n', maxsplit=-1)
    #         # print("excel中的数据:{}".format(newList))
    #     except Exception as e:
    #         continue
    #
    #     for item2 in Files:
    #         total += 1
    #         # print("当前文件夹:{} 当前附件:{} 当前文件路径:{}".format(item1, item2, os.path.join(folderFile, item2)))
    #
    #         # 判断文件是否全称存在, 名称全匹配上跳过
    #         ifmatch = newList.count(item2)
    #
    #         # 用户存放模糊匹配到的 附件集合
    #         fileSelect = []
    #
    #         if ifmatch >= 1:
    #             num1 += 1
    #         else:
    #             for i in newList:
    #                 if item2[:item2.index('.')] in i:
    #                     fileSelect.append(i)
    #             # print(fileSelect)
    #
    #             # 处理附件名字
    #             if len(fileSelect) == 1:
    #                 # 替换附件名字
    #                 """
    #                 print("修改前:{} 修改后:{}".format(os.path.join(folderFile, item2),
    #                                              os.path.join(folderFile, fileSelect[0])))
    #                                             """
    #                 try:
    #                     os.rename(os.path.join(folderFile, item2), os.path.join(folderFile, fileSelect[0]))
    #                 except Exception as e:
    #                     pass
    #                 num2 += 1
    #             if len(fileSelect) > 1:
    #                 # 附件名称相同，打印日志
    #                 print("附件名称相同，附件地址{}:".format(os.path.join(folderFile, item2)))
    #                 num3 += 1
    #             if len(fileSelect) == 0:
    #                 print("附件未找见，附件地址{}:".format(os.path.join(folderFile, item2)))
    #                 num4 += 1
    #
    # print("总数据:{}, 无需处理的:{}, 已处理的:{}, 附件名称重复:{}, 附件未找见:{} ".format(total, num1, num2, num3, num4))
    excelpath = input('获取手机号excel地址:')
    oo = excel(excelpath)
    phone, sheet_num = oo.findData(0, '周洪伟')
    print(phone)
